<?php
/*
# ------------------------------------------------------------------------
# JA Job Board Package
# ------------------------------------------------------------------------
# Copyright (C) 2004-2010 JoomlArt.com. All Rights Reserved.
# @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
# Author: JoomlArt.com
# Websites: http://www.joomlart.com - http://www.joomlancers.com.
# ------------------------------------------------------------------------
*/

jimport( 'joomla.application.component.model' );

/**
 * @package Joomla
 * @subpackage JobBoard
 */
class JAJobBoardModelJAJobs extends JModel
{
    var $_data;
    var $_table;
    var $_pagination;
    var $_fieldList;
    var $_fieldListEdit;
    var $_table_name='ja_jobs';
    
    /**
    * Return the query is used to retrive all row from database
    * @return string The query is used 
    */
    
     function getItems($where_more=''){
    	global $mainframe, $jbconfig, $option;
    	
    	$db = JFactory::getDBO ();

		$request = $_REQUEST;
		$filter_field_keys = array();
		if($request){
			$filter_field_keys = array_keys($request);
		}
				
    	$user = JFactory::getUser();
    	$statistics = array();
    	$statistics['status'] = JRequest::getVar('filter_status');
    	$statistics['created_date'] = JRequest::getVar('filter_duration');
    	    	    	
		$limit = $mainframe->getUserStateFromRequest( JBCOMNAME .".jajobs.limit",	'limit',	'20',	'int' );
		$limitstart = $mainframe->getUserStateFromRequest( JBCOMNAME .".jajobs.limitstart",	'limitstart',	'0',	'int' );
		
    	$db = JFactory::getDBO();
    	
    	$search	= $mainframe->getUserStateFromRequest( JBCOMNAME .".jajobs.search",			'search',			'',				'string' );
		$search = trim(JString::strtolower ( $search ));
    	
		$is_hotjob	= $mainframe->getUserStateFromRequest( $option.".jajobs.is_hotjob",	'is_hotjob',		'',	'cmd' );
		
		
    	$this->getFieldList();
    	$this->getEditFieldList(' and f.frontend_hidden=0');
    	
    	$filter_order = 	JRequest::getVar('filter_order', '');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( $option.".jajobs.filter_order_Dir",	'filter_order_Dir',	"DESC",	'word' );				
    	
    	$where = array();
    	
    	// just in case filter_order get's messed up
		$join = '';
		$orderby = '';
		$where = "";
		$fields_show = '';
				
		$join_clause_field = array ();
		$join_clause_table = array ();
		$field_temp = array();
		$subwhere = array ();		
		$where_clause = array();		
				
		
		$join_clause_table['profi'] =  "\n INNER JOIN #__ja_profiles as  profi";
		$join_clause_table['rs'] =  "\n INNER JOIN #__users as rs";
		
		$join_clause_field['profi'] = "  ON profi.user_id=a.user_id ";
		$join_clause_field['rs'] = " ON rs.id=a.user_id ";
		
		JB_Admin_Build_where($join_clause_table, $join_clause_field, $field_temp, $where_clause, $this->_fieldListEdit, $filter_field_keys, $request, $search);			
		
        if($is_hotjob!='') $where_clause[] = 'a.is_hotjob='.(int)$is_hotjob;
        
    	
        
    	switch ($statistics['created_date'])
    	{
    		case 'today':
    			$where_clause[] = '(DATEDIFF(a.created_date,now())=0)';
    			break;
    		case 'week':
    			$where_clause[] = '(EXTRACT(WEEK FROM a.created_date)=EXTRACT(WEEK FROM now()))';
    			break;	
    		case 'month':
    			$where_clause[] = '(EXTRACT(MONTH FROM a.created_date)=EXTRACT(MONTH FROM now()))';
    			break;
    	}
        
        switch ($statistics['status'])
    	{
    		case 'approved':
    			
    			$where_clause[] = '(a.status="Approved")';
    			break;
    		case 'nonapproved':
    			$where_clause[] = '(a.status="Non-Approved")';
    			break;	
    		case 'waiting':
    			$where_clause[] = '(a.status="Waiting")';
    			break;
    		case 'expired':
    			$where_clause[] = '(a.status="Expired")';
    			break;
    		
    	}
    	
        // just in case filter_order get's messed up       
		if ($filter_order) {
			$orderby =$filter_order .' '. $filter_order_Dir;
		} else {
			$orderby = " a.id desc" ;
		}
		
     	if($field_temp){
			$fields_show = ', '. implode(', ', $field_temp);	
		}
		
		foreach ($join_clause_table as $j=>$table){
			$join .= "\n $table ";
			if(isset($join_clause_field[$j])){
				$join .= $join_clause_field[$j];
			}
		}
			
		if ($where_clause) {
			$where = ' AND ' .implode(' AND ', $where_clause);
		}
        
        /* Count items */
		$query = "SELECT count(distinct a.id) ".
					"\n FROM #__$this->_table_name as a" .				
					$join.
					"\n WHERE 1=1 ".  $where. $where_more;	
		
		$db->setQuery ( $query );//echo $db->getQuery();exit;
		$total = $db->loadResult ();
		
		jimport ( 'joomla.html.pagination' );		
		$this->_pagination = new JPagination ( $total, $limitstart, $limit );
		
		
		/* Get all items */
		$query = "SELECT distinct a.id, a.*, profi.company, profi.logo, profi.id as profileid $fields_show" . 
					"\n FROM #__$this->_table_name as a" .				
					$join.
					"\n WHERE 1=1 ".  $where. $where_more.
					"\n ORDER BY ". $orderby . ', a.effected_date desc';
		$db->setQuery ( $query, $limitstart, $limit );			
					
		$items = $db->loadObjectList ();
				
		
		$jconfig = new JConfig();
		$timezone = $jconfig->offset;
		
		if ($items) {
			foreach ( $items as $k => $job ) 
				if (isset($job->id)){					
					$job->title = JFilterInput::_decode ( $job->title );
					$job->job_detail = JFilterInput::_decode ( $job->job_detail );								
								
					$enable_timestamp = isset ( $jbconfig ['general'] ) ? $jbconfig ['general']->get ( 'posts_show_published_date', 1 ) : 1;
				
					if ($enable_timestamp && $job->effected_date) {
						$job->effected_date = generatTimeStamp ( strtotime ( $items [$k]->effected_date ) );
					} elseif ($job->effected_date) {
						if (! $user)
							$job->effected_date = generateDate ( $items [$k]->effected_date );
						else
							$job->effected_date = generateDate ( date ( 'Y-m-d H:i:s', strtotime ( $items [$k]->effected_date ) - date ( 'Z' ) + $user->getParam ( 'timezone', $timezone ) * 60 * 60 ) );
					}
					
					if ($items [$k]->created_date)
					{
						if (! $user)
							$job->created_date = generateDate ( $items [$k]->created_date );
						else
							$job->created_date = generateDate ( date ( 'Y-m-d H:i:s', strtotime ( $items [$k]->created_date ) - date ( 'Z' ) + $user->getParam ( 'timezone', $timezone ) * 60 * 60 ) );
					}
					
					if ($this->_fieldList) {
						foreach ( $this->_fieldList as $field ) {
							
							$job->{$field->field_name} = JFilterInput::_decode ( $job->{$field->field_name} );
								
							if ($field->field_type == 'TEXTFIELD'){
								$job->{$field->field_name} = JFilterInput::clean ( $job->{$field->field_name} );
							}							

							
							if ($field->is_referred > 0 && $job->{$field->field_name}) {
								
								
								$values = explode(',', $job->{$field->field_name});
								foreach ($values as $i=>$v){
									$values[$i] = $db->Quote($v);	
								}								
								$values = implode(',', $values);
								if(!$values) $values=0;
								
								$sql = "SELECT {$field->referred_value} ".
										"\n FROM #__{$field->referred_table} ".
										"\n WHERE {$field->referred_key} in ($values)";
								$db->setQuery ( $sql );
								$result = $db->loadResultArray ();
								if ($result) {
									$job->{$field->field_name} = implode(', ', $result);
								}
								else{
									$job->{$field->field_name} = '';
								}
								
								
							} elseif (in_array ( $field->field_type, array ('RADIO', 'SELECT', 'MSELECT' ) ) && $job->{$field->field_name}) {
								
								$values = explode(',', $job->{$field->field_name});
								foreach ($values as $i=>$v){
									$values[$i] = $db->Quote($v);	
								}
								$values = implode(',', $values);
								$field_id = $field->table_name . '_' . $field->field_name;
								
								$sql = "SELECT text ".
										"\n FROM #__ja_codes ".
										"\n WHERE value in ($values) and field_id='$field_id' ";
																
								$db->setQuery ( $sql );
								$result = $db->loadResultArray ();
								
								if ($result) {
									$job->{$field->field_name} = implode(', ', $result);
								}
								else{
									$job->{$field->field_name} = '';
								}
							}
					}
					
					$items [$k] = $job;
				}
			}
		}
		return $items;
    
    }     
     function getListId_from_ListName($field)
    {
    	$db = JFactory::getDBO();
    	$temp = explode(',',$field->value);
    	if (is_array($temp))
    		for ($i=0;$i<count($temp);$i++)
    			$temp[$i] = "'".$temp[$i]."'";
    	$field->value = implode(',',$temp);
    	if( $field->is_referred)
    	{
    		$query = "SELECT $field->referred_key FROM #__$field->referred_table WHERE $field->referred_value IN ($field->value)";	
    		$db->setQuery($query);
    		$result = $db->loadObjectList();
    		$temp = array();
    		if (is_array($result))
    			for ($i=0;$i<count($result);$i++)
    			{
    				$temp[] = $result[$i]->{$field->referred_key};
    			}
    		$temp = implode(',',$temp);		
			if ($this->check_multi_level($field->referred_table))
			{
				$temp = $this->getSubId($field->referred_table,$temp);
				
			}
    	}
    	elseif ((trim($field->field_type)=='SELECT') || (trim($field->field_type)=='MSELECT')) 
    	{
    		$query = "SELECT value FROM #__ja_codes WHERE text IN ($field->value)";
    		$db->setQuery($query);
    		$result = $db->loadObjectList();
    		$temp = array();
    		if (is_array($result))
    			for ($i=0;$i<count($result);$i++)
    			{
    				$temp[] = $result[$i]->{$field->referred_key};
    			}
    		$temp = implode(',',$temp);	
    	}
		return $temp;    
    }
    /**
	* Get configuration item
	* @return JobboardTableJA_Jobs Table object
	*/
	function getItem(){
		static $item;
		if (isset($item)) {
			return $item;
		}
		
        $table =& $this->_getTable(); 
       	
		// Load the current item if it has been defined
		$edit	= JRequest::getVar('view',true);
		$cid = JRequest::getVar( 'cid', array(0), '', 'array' );
		JArrayHelper::toInteger($cid, array(0));
		
		if ($edit) {
			
				$table->load($cid[0]);
//			else 
//			{
//				
//				$table->bind($post);
//			}
		}
		
	    $item = $table;
	    return $item;	    
	}
	
    /**
    * @desc Get all fields that will be displayed on edit form
    */
    function getEditFieldList($where = ''){        
        if(!$this->_fieldListEdit){	       
	        $this->_fieldListEdit = getEditFieldList($this->_table_name);
        }   
		return $this->_fieldListEdit ;       
    }
    
    function getFieldList($where=''){    	    	
    	if( !$this->_fieldList){	       
	        $this->_fieldList = getFieldList($this->_table_name); 
    	}
    	return  $this->_fieldList;
    }
    
    
    /**
    * Get field table instance
    * @return JTable Configuration table object
    */
    function &_getTable(){
        if($this->_table == null){
        	$this->_table = &JTable::getInstance('ja_jobs', 'JobBoardTable');
        }
		return $this->_table;
	}
	
	/**
	* Get page navigator object
	* @return 
	*/
	function &getPagination(){
		if ($this->_pagination == null) {
			$this->getItems();
		}
		return $this->_pagination;
	}
	
	/**
	* Remove field record
	* @return int Number of removed records
	*/
	function remove($task = 'remove'){
	    $cid = JRequest::getVar('cid',array(), 'post', 'array');
	    $n =  count($cid);
	    JArrayHelper::toInteger( $cid );
	    
	    if($n){
            if($task == 'remove'){
                $query = "UPDATE #__ja_applications SET job_id = null WHERE job_id IN (". implode(" OR id = ", $cid) .")";
                $query .= "; UPDATE #__ja_shortlist SET item_id = null, listtype = null WHERE listtype = 'ja_jobs' AND item_id IN (". implode(" OR id = ", $cid) .")";
                $query .= "; DELETE FROM #__ja_jobs WHERE id = ". implode(" OR id = ", $cid);
                
                $db = &JFactory::getDBO();
                $db->setQuery($query);
                return $db->queryBatch();
            }
        }
		
		return 0;
	}
	
	
	/**
	* Checkout item
	* 
	*/
	function checkout($uid = null){
		$id = JRequest::getVar('cid', array(0), '', 'array');
		JArrayHelper::toInteger( $id, array(0) );

		// Make sure we have a user id to checkout the article with
		if (is_null($uid)) {
			$user	=& JFactory::getUser();
			$uid	= $user->get('id');
		}

		// Lets get to it and checkout the thing...
		$item	= $this->getItem();
		if(!$item->checkout($uid, $id[0])) {
			return false;
		}

		return true;
	}
	
	/** 
	* Store configuration item
	* @param array The post array
	*/
	function store(){
	    // Initialize variables
	    global $jbconfig;
	    $controller = 'jajobs';
		$db		=& JFactory::getDBO();
		$row	=& $this->getItem();
		$old_cat_id = $row->cat_id;
		$old_is_hotjob = $row->is_hotjob;
        $old_location_id = $row->location_id;
        $post	= $this->getState('request');
        JRequest::setVar('cid[]', $post['user_id']);
        //print_r($post);
        //$temp = JModel::getInstance('JAEmployers', 'JAJobBoardModel');
        //$employer = $temp->getProfileItem();
        $employer = getProfileFromUserId($post['user_id']);
        
        if ($row->id == null)
        {
			$newjob = 1;
			unset($post["id"]);
		}
		else
			$newjob = 0;
		
		foreach ( $post as $k => $value ) {
			
			if ($k != 'cid' && is_array ( $value )) {				
				$post [$k] = implode ( ',', $value );
			}
			elseif ($k == 'job_detail') {
				$post [$k] = trim($value);
				// Clean text for xhtml transitional compliance
				$post [$k] = str_replace ( '<br>', '<br />', $post [$k] );
			}
			else {
				$post [$k] = $value = trim($value);
			}
		}
		
		$pre_status = $row->status;
				
		if (!$row->bind( $post )) {
            JError::raiseWarning(403,'');
			return false;
		}
		if ($row->spec_field)
		{
           	if (!assign_spec_field($controller,$row))
           		return false;
		}
		if (isset($row->editor_field) && $row->editor_field)
			assign_editor_field( $row);
		
		if (!$row->check())
		{
            JError::raiseWarning(403,'');
            return false;
		}
		
		if ($employer->id && $row->is_hotjob!=$old_is_hotjob)
		{
			if (!$this->check_posting_amount($row->is_hotjob,$row->user_id))
			{
				if ($row->is_hotjob=='0')
					JError::raiseWarning(403,JText::_('This user do not have standard posting quantity'));
				else 
					JError::raiseWarning(403,JText::_('This user do not have premium posting quantity'));
				return false;
			}
			
		}
		if (!$row->store())
		{
			JError::raiseWarning(403,'');
			return false;
		}
		
		if(!$employer->id) return $row->id;
		
		if ($newjob)
        {
        	JRequest::setVar('is_newjob','1');
        	$this->updateAcc($row,$row->is_hotjob);
			$this->checkActive($row);
			if ($jbconfig['emails']->get('enabled'))
			{
				$this->adminMailNewJob($row->id,$row->user_id);
				auto_send_mail_to_jobseeker_about_job($row,$row->cat_id,$row->location_id);
			}
        }
        else 		
        {	
        	if (($old_location_id!=$row->location_id)||($old_cat_id!=$row->cat_id))
        	{
        		$arr_cat_id = explode(',',$row->cat_id);
        		$old_arr_cat_id = explode(',',$old_cat_id);
        		if ($old_cat_id!=$row->cat_id)
        			$diff_cat = implode(',',array_diff($arr_cat_id,$old_arr_cat_id));
        		else 
        		{
        			if ($old_location_id==$row->location_id)
        				$diff_cat = '';
        			else 
        				$diff_cat = implode($arr_cat_id);
        		}
        		if ($old_location_id!=$row->location_id)
        			$diff_location = $row->location_id;
        		else
        		{ 
        			if ($diff_cat=='')
        				$diff_location = '';
        			else 
        				$diff_location = $row->location_id;
        		}
        		if (($diff_cat!='')&&($diff_location!=''))
        			auto_send_mail_to_jobseeker_about_job($row,$diff_cat,$diff_location);
        	}
        	JRequest::setVar('is_newjob','0');
        }
        
		$status = $row->status;
		if ($status!=$pre_status){
			if ($status=='Approved')
			{
				if ($jbconfig['emails']->get('enabled'))
					$this->sendMail_Approved($row->id,$row->user_id,'JobMailApproved');
				require_once JPATH_COMPONENT_SITE.DS.'models'.DS.'jausergroup.php';
			}
			else 
			{
				if ($jbconfig['emails']->get('enabled'))
					require_once JPATH_COMPONENT_SITE.DS.'models'.DS.'jausergroup.php';
				$this->sendMail_Approved($row->id,$row->user_id,'JobMailNonApproved');
			}
		}
		if ($row->is_hotjob!=$old_is_hotjob)
			$this->change_is_hotjob_value($row->is_hotjob,$row->user_id);
		$row->checkin($row->id);
		
		return $row->id;
	}
	
	function sendMail_Approved($job_id,$user_id,$emailtemplate)
	{
    	
    	$user = JFactory::getUser($user_id);
    	$arr_link = array();
    	$arr_link['users'] = $user_id;
    	$arr_link['ja_jobs'] = $job_id;
    	$profile = getProfileFromUserId($user_id);
    	$arr_link['ja_profiles'] = $profile->id;
    	$mail = get_EmailTemplate($arr_link,$emailtemplate);
    	if ($mail)
    	{
    		sendMail($user->email,$user->name,$mail->subject,$mail->content,'',$mail->EmailFromAddress, $mail->EmailFromName);
    	}		
	}
        
    /**
    * approved one job
    * 
    */
    function approved($status){
    	global $jbconfig;
    	$db = &JFactory::getDBO();
        $cid = JRequest::getVar('cid',array(), 'post', 'array');
        $n =  count($cid);
        JArrayHelper::toInteger( $cid );
        $query = "SELECT * FROM #__ja_jobs WHERE id = ". implode(" OR id = ", $cid);
        $db->setQuery($query);
        $result = $db->loadObjectList();
        if ($jbconfig['emails']->get('enabled'))
        for ($i=0;$i<count($result);$i++)
        {
	        if ($status=='Approved')
	        	$this->sendMail_Approved($result[$i]->id,$result[$i]->user_id,'JobMailApproved');
	        else 
	        	$this->sendMail_Approved($result[$i]->id,$result[$i]->user_id,'JobMailNonApproved');
        }	
        $query = "UPDATE #__ja_jobs SET status = '$status' WHERE id = ". implode(" OR id = ", $cid);
        
        $db->setQuery($query);
        return $db->query();
    }
    
    function getLocation(){
    	$db		=& JFactory::getDBO();
    	$query = "SELECT id, name FROM #__ja_location WHERE (parent_id = 0 OR ISNULL(parent_id)) AND published = 1";
        $db->setQuery($query);//print_r($db->getQuery($query));exit;
        $locations = $db->loadObjectList();
        return $locations;
    }
	function checkActive($row)
	{
		if (!$row->user_id)
			return;
		$active = true;
		if (($row->effected_date>date('Y-m-d H:i:s')) || (date('Y-m-d H:i:s')- $row->effected_date)>30 )
			$active = false;
		if ($row->status!='Approved')
			$active = false;
			
		if ($active)	
		{
			$db = JFactory::getDBO();
			$query = "UPDATE #__ja_employers SET hiring=1 WHERE user_id=$row->user_id";
			$db->setQuery($query);
			$db->query();
		}
	}
	
	function updateAcc($job,$hotjob)
	{
		
		global $jbconfig;
		$db = JFactory::getDBO();
		if (!$job->user_id)
			return;
		$user = JFactory::getUser($job->user_id);
		$query = "SELECT * FROM #__ja_employers WHERE user_id=".(int)$user->id;
		$db->setQuery($query);
		$employer = $db->loadObjectList();
		if ($hotjob)
		{
			if ($jbconfig['billing']->get('premium_posting_fee_enabled'))
			{	
				$num = $employer[0]->premium_posts_balance - 1;
				$query = "UPDATE #__ja_employers SET premium_posts_balance=$num WHERE user_id=$user->id";
				$db->setQuery($query);
				$db->query();
			
			}
		}
		else 
		{
			if ($jbconfig['billing']->get('posting_fee_enabled') && $employer)
			{
				$num = $employer[0]->standard_posts_balance - 1;
				$query = "UPDATE #__ja_employers SET standard_posts_balance=$num WHERE user_id=$user->id";
				$db->setQuery($query);
				$db->query();
			}
		}
		
	}  
    function adminMailNewJob($job_id,$user_id,$email_template='AdminMailNewJob')
    {
    	if (!$user_id)
    		return;
    	$emailConfig = JobBoardConstant::getEmailConfig();
    	$user = JFactory::getUser();
    	$arr_link = array();
    	$arr_link['ja_jobs'] = $job_id;
    	$arr_link['users'] = $user_id;
    	$mail = get_EmailTemplate($arr_link, $email_template);
    	if ($mail)
    		sendMail($emailConfig['admin_email'],$emailConfig['admin_name'],$mail->subject,$mail->content,'',$mail->EmailFromAddress, $mail->EmailFromName);
    }	  
    function change_is_hotjob_value($value,$user_id)
    {
    	global $jbconfig;
    	if ($value=='1')
    	{
    		$premium = -1;
    		$standard = 1;
    	}
    	else 
    	{
    		$premium = 1;
    		$standard = -1;
    	}
    	$db = JFactory::getDBO();
    	if ($jbconfig['billing']->get('posting_fee_enabled', 0))
    	{
    		$query = "UPDATE #__ja_employers SET standard_posts_balance=standard_posts_balance+$standard WHERE user_id=$user_id";
    		$db->setQuery($query);
    		$db->query();
    	}
    	if ($jbconfig['billing']->get('premium_posting_fee_enabled', 0))
    	{
    		$query = "UPDATE #__ja_employers SET premium_posts_balance=premium_posts_balance+$premium WHERE user_id=$user_id";
    		$db->setQuery($query);
    		$db->query();
    	}
    }
    function check_posting_amount($value,$user_id)
    {
    	global $jbconfig;
    	$db = JFactory::getDBO();
    	$query = "SELECT * FROM #__ja_employers WHERE user_id=$user_id";
    	$db->setQuery($query);
    	$employer = $db->loadObject();
    	if ($employer && $employer->standard_posts_balance==0&&$value=='0' && $jbconfig['billing']->get('posting_fee_enabled', 0))
    		return false;
    	elseif ($employer&& $employer->premium_posts_balance==0&&$value=='1' && $jbconfig['billing']->get('premium_posting_fee_enabled', 0)) 
    		return false;
    	return true;
    }
    
    function remove_attach()
    {
    	remove_attach('ja_jobs');

    }
    function download(){
        $cid = JRequest::getVar( 'cid', array(0), '', 'array' );
		JArrayHelper::toInteger($cid, array(0));
        $table =& $this->_getTable(); 
		$table->_tbl_key = 'id';
        $table->load($cid[0]);
        
        $file_attachment 		= JRequest::getVar("field_name", "");
        $table->file_attachment = $table->{$file_attachment};        
        $file_name = explode('/',$table->file_attachment);
        
        if( $table->file_attachment) {
        	$file = JPATH_SITE.DS.str_replace('/','\\',$table->file_attachment);
	        if (file_exists(str_replace('/','\\',$file)))
	        	$file = str_replace('/','\\',$file);
	        else 
        		$file = str_replace('\\','/',$file);
            
        	header('Content-Description: File Transfer');
		    header('Content-Type: application/octet-stream');
		    header('Content-Disposition: attachment; filename="'.substr ( $file_name [count ( $file_name ) - 1], 14 ) .'"');
		    header('Content-Transfer-Encoding: binary');
		    header('Expires: 0');
		    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		    header('Pragma: public');
		    header('Content-Length: ' . filesize($file));
		    ob_clean();
		    flush();
		    readfile($file);
        }
    }
    
}
?>